﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess_Layer
{
    public static class Bonus_DAC
    {
        public static List<BonusDTO> getBonusByProdId(int productID)
        {
            List<BonusDTO> bonuses = new List<BonusDTO>();

            List<SqlParameter> sqlParams = makeGetBonusByProductIDParams(ref productID);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_Bonus_By_ProductID", sqlParams);

                while (rdr.Read())
                {
                    BonusDTO bonus = createBonus(ref rdr);
                    bonuses.Add(bonus);
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return bonuses;
        }
        private static BonusDTO createBonus(ref SqlDataReader rdr)
        {
            BonusDTO bonus = new BonusDTO();
            bonus.ID = int.Parse(rdr["ID"].ToString());
            bonus.Name = rdr["Name"].ToString();
            bonus.Total = int.Parse(rdr["Total"].ToString());
            bonus.Score = int.Parse(rdr["Score"].ToString());
            bonus.Image = rdr["Image"].ToString();

            return bonus;
        }
        private static List<SqlParameter> makeGetBonusByProductIDParams(ref int productID)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@productID", SqlDbType.Int);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = productID;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static List<BonusDTO> getBonusByProdIds(string prodIDs)
        {
            List<BonusDTO> bonuses = new List<BonusDTO>();

            foreach (string prodId in prodIDs.Split(','))
            {
                bonuses.AddRange(getBonusByProdId(int.Parse(prodId)));
            }

            return bonuses;
        }

        public static BonusDTO getBonusById(int bonusID)
        {
            BonusDTO bonus = null;
            List<SqlParameter> sqlParams = makeGetBonusByIDParams(ref bonusID);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_Bonus_By_ID", sqlParams);

                while (rdr.Read())
                {
                    bonus = createBonus(ref rdr);
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return bonus;
        }

        private static List<SqlParameter> makeGetBonusByIDParams(ref int bonusID)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@id", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = bonusID;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static bool insert(BonusDTO newBonus)
        {
            List<SqlParameter> sqlParams = makeInsertParams(newBonus);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int count;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Insert_Bonus", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                count = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (count == 1);
        }
        private static List<SqlParameter> makeInsertParams(BonusDTO newBonus)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@name", SqlDbType.NVarChar, 50);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newBonus.Name;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@image", SqlDbType.NVarChar, 50);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newBonus.Image;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@total", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newBonus.Total;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@score", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newBonus.Score;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static bool update(BonusDTO updatedBonus)
        {
            List<SqlParameter> sqlParams = makeUpdateParams(updatedBonus);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int count;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Update_Bonus", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                count = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (count == 1);
        }
        private static List<SqlParameter> makeUpdateParams(BonusDTO updatedProd)
        {
            List<SqlParameter> sqlParams = makeInsertParams(updatedProd);

            SqlParameter inParam = new SqlParameter("@id", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = updatedProd.ID;
            sqlParams.Add(inParam);

            return sqlParams;
        }
    }
}
